import os
import sys

import pandas as pd
# import pymysql # 原生驱动
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import sessionmaker

from src.spark_edu_rag.utils import config_ini, get_logger, resource_loader


# tudo 1、自定义MySQL客户端
class MysqlClient(object):
    # 初始化数据库连接、创建连接，设置字符集为utf8mb4
    def __init__(self):
        self.logger = get_logger(__name__)
        self._config = config_ini
        self._init_engine()
        self._init_session()

    def _init_engine(self):
        db_url=(
                    f"mysql+pymysql://"
                    f"{self._config.MYSQL.USER}:{self._config.MYSQL.PASSWORD}@"
                    f"{self._config.MYSQL.HOST}:{self._config.MYSQL.PORT}/"
                    f"{self._config.MYSQL.DATABASE}?charset={self._config.MYSQL.CHARSET}"
                )
        try:
            self.engine = create_engine(
                db_url,
                pool_size=10,
                max_overflow=20
            )
        except SQLAlchemyError as e:
            self.logger.error(f"SQLAlchemy 引擎初始化失败：{e}")
            raise e

    def _init_session(self):
        """初始化会话，创建会话工厂"""
        try:
            self.Session = sessionmaker(bind=self.engine)
            self.session = self.Session()
        except SQLAlchemyError as e:
            self.logger.error(f"SQLAlchemy 会话初始化失败：{e}")
            raise e

    def create_table(self):
        # 创建表
        create_table_sql = """
        CREATE TABLE IF NOT EXISTS ai_science_question (
            id INT AUTO_INCREMENT PRIMARY KEY,
            subject_name VARCHAR(255),
            question TEXT,
            answer TEXT,
            created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
        """
        try:
            with self.engine.connect() as conn:
                conn.execute(text(create_table_sql))
                conn.commit()
                self.logger.info("ai_science_question 表创建成功")
        except SQLAlchemyError as e:
            self.logger.error(f"ai_science_question 表创建失败：{e}")
            raise e

    def insert_data(self, file_name: str):
        try:
            with resource_loader.get_path(file_name) as csv_path:
                df = pd.read_csv(csv_path, encoding='utf-8')
                df_renamed = df.rename(columns={
                    "学科名称": "subject_name",
                    "问题": "question",
                    "答案": "answer"
                })
                df_renamed.to_sql(
                    name="ai_science_question",
                    con=self.engine,
                    index=False,
                    if_exists="append",
                    chunksize=1000,
                    method="multi"
                )
            self.logger.info(f"数据插入成功，共插入 {len(df)} 条记录")
        except Exception as e:
            self.logger.error(f"数据插入失败：{e}")
            raise e

    def fetch_questions(self):
        try:
            with self.engine.connect() as conn:
                result = conn.execute(text("SELECT question FROM ai_science_question"))
                rows = result.fetchall()
            self.logger.info(f"共查询到 {len(rows)} 条question记录")
            return rows
        except Exception as e:
            self.logger.error(f"数据查询question失败：{e}")
            raise e

    def fetch_answer(self, question_param: str):
        try:
            with self.engine.connect() as conn:
                result = conn.execute(text("SELECT answer FROM ai_science_question where question like :question_param"), {"question_param": f"%{question_param}%"})
                rows = result.fetchall()
            # 如果有数据，则返回第一条数据
            self.logger.info(f"共查询到 {len(rows)} 条answer记录")
            return rows[0] if rows else None
        except Exception as e:
            self.logger.error(f"数据查询answer失败：{e}")
            raise e


    def close(self):
        try:
            if self.session:
                self.session.close()
            if self.engine:
                self.engine.dispose()
            self.logger.info("MySQL 客户端连接关闭成功")
        except Exception as e:
            self.logger.error(f"MySQL 客户端连接关闭失败：{e}")
            raise e

    def __del__(self):
        try:
            self.close()
        except Exception as e:
            self.logger.error(f"MySQL 客户端连接关闭失败：{e}")

mysql_client = MysqlClient()

if __name__ == '__main__':
    # mysql_client.create_table()
    # mysql_client.insert_data("JP学科知识问答.csv")
    questions = mysql_client.fetch_questions()
    print(questions)
    # answer = mysql_client.fetch_answer("pycharm资源管理区域文件的显示更新")
    # print(answer)
